 
-- **************************************************************************
--    Project Name:   经营指标-指标库应用网点维度月表
--    Job Name:       jms_dwm.dm_overall_target_network_sum_month_dt
--    Author :        季修魁
--    date：          2023/06/27
-- **************************************************************************
-- **************************************************************************

------------
insert overwrite table jms_dwm.dwm_overall_target_network_sum_month_dt partition (dt)
select a.stat_date
       ,b.network_type
       ,a.network_code
       ,a.network_name
       ,b.fran_code
       ,b.fran_name
       ,b.agent_code
       ,b.agent_name
       ,b.virt_code
       ,b.virt_name
       ,a.kpi_code
       ,a.kpi_name
       ,a.kpi_type
       ,a.kpi_fz
       ,a.kpi_fm
       ,a.kpi_values
       ,a.kpi_param
       ,c.principal_name
       ,d.principal
       ,b.principal
       ,a.stat_date    as  dt
FROM (
      select trunc(stat_date,'mm')  as stat_date
             ,network_code
             ,network_name
             ,kpi_code
             ,kpi_name
             ,kpi_type
             ,kpi_param
             ,sum(kpi_fz)  as kpi_fz
             ,case when kpi_type = 2 then sum(kpi_fm) else max(kpi_fm) end  as kpi_fm
             ,sum(kpi_values) as kpi_values
        from jms_dwm.dwm_overall_target_network_sum_mid_dt
       where dt>=trunc('{{ execution_date | cst_ds }}','mm') and dt<='{{ execution_date | cst_ds }}'
       group by trunc(stat_date,'mm')
               ,network_code
               ,network_name
               ,kpi_code
               ,kpi_name
               ,kpi_type
               ,kpi_param
     union all
      select trunc(stat_date,'mm')  as stat_date
             ,network_code
             ,network_name
             ,kpi_code
             ,kpi_name
             ,kpi_type
             ,kpi_param
             ,sum(kpi_fz)  as kpi_fz
             ,case when kpi_type = 2 then sum(kpi_fm) else max(kpi_fm) end  as kpi_fm
             ,sum(kpi_values) as kpi_values
        from jms_dwm.dwm_overall_target_network_sum_mid_dt
       where dt>=trunc(add_months('{{ execution_date | cst_ds }}',-1),'mm') and dt<=last_day(add_months('{{ execution_date | cst_ds }}',-1))
         and substr('{{ execution_date | cst_ds }}',9,2) in ('01','02')
       group by trunc(stat_date,'mm')
               ,network_code
               ,network_name
               ,kpi_code
               ,kpi_name
               ,kpi_type
               ,kpi_param
     )  a
left join jms_dim.dim_network_whole_massage b
  on a.network_code = b.code
left join (select agent_code,agent_name,virt_code,virt_name,split(principal,"/")[1]  as principal_name
             from jms_dim.dim_network_whole_massage where network_type =2   ---代理区负责人
            group by agent_code,agent_name,virt_code,virt_name,split(principal,"/")[1]
          ) c
  on b.agent_code = c.agent_code
left join (select code,name,principal from jms_dim.dim_network_whole_massage where network_type = '3'
            group by code,name,principal
          ) d
  on b.fran_code =d.code
distribute by 1;
